package com.fastloan.jn.mgt.common.util.excelutil;

import com.fastloan.jn.mgt.common.util.excelutil.annotation.ExcelTableHeader;
import com.fastloan.jn.mgt.common.util.excelutil.exception.ExpertExcelException;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.commons.beanutils.BeanUtilsBean2;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.converters.DateConverter;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

import static com.fastloan.jn.mgt.common.util.DateTimeUtil.NORM_DATETIME_PATTERN;


/**
 * excel处理工具
 *
 * @author yanwe
 *         createTime 2017-04-2017/4/5 15:27
 */
public class ExcelUtil {

    /**
     * 下载excel
     *
     * @param response  response
     * @param excelName 文件名
     * @param wb        excel文档对象
     */
    public static void outPut(HttpServletResponse response, String excelName, HSSFWorkbook wb) {
        //输出Excel文件
        try {
            OutputStream output = response.getOutputStream();
            response.reset();
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(excelName, "UTF-8") + ".xls");
            response.setContentType("application/msexcel");
            response.setCharacterEncoding("UTF-8");
            wb.write(output);
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建默认形式的表格 第一行默认标题
     *
     * @param list 数据
     * @param sheetName sheet名称
     * @param title 标题名称
     * @param <T> 类型
     * @return HSSFWorkbook
     */
    public static <T> HSSFWorkbook createTable(List<T> list, String sheetName, String title) throws ExpertExcelException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        createTableSheet(workbook,list,sheetName,title);
        return workbook;
    }

    /**
     * 创建默认形式的表格 第一行默认标题
     *
     * @param wb HSSFWorkbook对象
     * @param list 数据
     * @param sheetName sheet名称
     * @param title 标题名称
     * @param <T> 类型
     */
    public static <T> void createTableSheet(HSSFWorkbook wb, List<T> list, String sheetName, String title) throws ExpertExcelException {
        if(list == null || list.isEmpty()) {
            return;
        }
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet(sheetName);
        //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1 = sheet.createRow(0);
        //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        HSSFCell cell = row1.createCell(0);
        //设置单元格内容
        cell.setCellValue(title);
        // 设置单元格类型
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平布局：居中
        cell.setCellStyle(cellStyle);
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        int size = filterField(list.get(0)).size();
        if (size == 0){
            throw new ExpertExcelException("没有任何属性设置表头注解");
        }
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0
                ,  size - 1));
        createTable(sheet,list);
        //自适应
        autoSize(sheet,1);
    }

    /**
     * 自适应列宽(取最宽)
     *
     * @param sheet sheet对象
     * @param skip 忽略多少行(从第一行开始)
     */
    public static void autoSize(HSSFSheet sheet,int skip){
        List<List<Integer>> allCellWidthList = new ArrayList<>();
        int rowSize = sheet.getLastRowNum();
        int cellSize = sheet.getRow(skip).getLastCellNum();
        for (int i = 0; i < cellSize; i++) {
            allCellWidthList.add(new ArrayList<>());
        }
        for (int rowIndex = skip; rowIndex < rowSize; rowIndex++) {
            HSSFRow row = sheet.getRow(rowIndex);
            for (int cellIndex = 0; cellIndex < cellSize; cellIndex++) {
                String stringCellValue = row.getCell(cellIndex).getStringCellValue();
                double width = 0;
                if(stringCellValue != null){
                    width = stringCellValue.getBytes().length * 1.05 * 256;
                }
                allCellWidthList.get(cellIndex).add((int) width);
            }
        }
        List<Integer> maxSizeList = allCellWidthList.parallelStream()
                .map(columnList -> columnList.stream().mapToInt(Integer::intValue).max().getAsInt())
                .collect(Collectors.toList());
        for (int i = 0; i < maxSizeList.size(); i++) {
            sheet.setColumnWidth(i,Math.min(maxSizeList.get(i),'\uff00'));
        }
    }

    /**
     * 只创建Table部分
     *
     * @param sheet sheet 对象
     * @param list 数据
     * @param <T> 类型
     */
    public static <T> void createTable(HSSFSheet sheet,List<T> list){
        if(list == null || list.isEmpty()) {
            return;
        }
        HSSFRow headerRow = sheet.createRow(sheet.getLastRowNum() + 1);
        for (Field field : filterField(list.get(0))) {
            headerRow.createCell(headerRow.getLastCellNum() == -1 ? 0 :headerRow.getLastCellNum())
                    .setCellValue(field.getAnnotation(ExcelTableHeader.class).name());
        }
        for (T bean : list) {
            HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
            for (Field field : filterField(bean)) {
                row.createCell(row.getLastCellNum() == -1 ? 0 :
                        row.getLastCellNum()).setCellValue(getFieldValue(field,bean));
            }
        }
    }

    private static <T> String getFieldValue(Field field, T bean) {
        Object value = null;
        try {
            BeanUtilsBean.setInstance(new BeanUtilsBean2());//必须设置，否则注册会失效
            DateConverter converter = new DateConverter();
            converter.setPattern(NORM_DATETIME_PATTERN);//设置时间转换的格式
            ConvertUtils.register(converter, Date.class);
            value = BeanUtils.getProperty(bean,field.getName());
        } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
            e.printStackTrace();
        }
        return value == null ? "" : value.toString();
    }

    private static <T> List<Field> filterField(T bean){
        return Arrays.stream(bean.getClass().getDeclaredFields())
                .filter(field -> field.isAnnotationPresent(ExcelTableHeader.class))
                .peek(field -> field.setAccessible(true))
                .collect(Collectors.toList());
    }

    public static HSSFWorkbook buildWork(){
        return new HSSFWorkbook();
    }

    public static HSSFSheet buildSeet(HSSFWorkbook workbook,String sheetName){
        return workbook.createSheet(sheetName);
    }

    public static void buildCell(String info,HSSFSheet sheet,HSSFWorkbook workbook,int statrRow ,int endRow,int startColumn,int endColumn,HSSFCellStyle cellStyle){
        HSSFRow row = getOrCreate(statrRow,sheet);
        if(endRow - statrRow > 0 || endColumn - startColumn > 0){
            sheet.addMergedRegion(new CellRangeAddress(statrRow,endRow,startColumn,endColumn));
        }
        HSSFCell cell = getOrCreate(startColumn,row);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(info);
    }

    public static void buildCell(String info,HSSFSheet sheet,HSSFWorkbook workbook ,int row , int col,HSSFCellStyle cellStyle){
        buildCell(info,sheet,workbook,row,row,col,col,cellStyle);
    }


    public static HSSFRow getOrCreate(int index,HSSFSheet sheet){
        if( null == sheet.getRow(index)) {return sheet.createRow(index); }
        return sheet.getRow(index);
    }

    public static HSSFCell getOrCreate(int index,HSSFRow row){
        if(null == row.getCell(index)){return row.createCell(index);}
        return row.getCell(index);
    }



}
